import re
import sqlite3 as db
import json
#计算单词语义相似度
import spacy
import Levenshtein
import time
import BaiduNLP
import random

import tools
from WordBean import WordBean

from QuestionBean import QuestionBean
import openpyxl

'''
    1、单词拼写，
        提取单词
    2、汉译英
        
    3、英译汉
    
    4、听音识词
        听取发音选择正确的单词
        
    5、选择题  从包含例句的单词中 提取  将此单词替换为__________,并随机抽取三个单词
        抽取的单词与之相关性定义：同为名词，字母数量相同
        
    6、词型填空
        与提示单词对比填入与之相关单词
    
'''


db_path = "/Users/xuyan/Desktop/公司项目/听力百分百/听力百分百单词/weici_ext.db"
wh_db_path = "/Users/xuyan/Desktop/WH_Lexicon.db"
wh_excel_path = "单词表.xlsx"
questionList = []
wordList = []
connSQL = 2# 1: sqlite  2:sqlserver
nlpCollect = ""
nlp = None
def initNLP():
    global nlp
    nlp = spacy.load('en_core_web_lg')
    if nlp != None:
        print("load en_core_web_lg module success")
    else:
        print("load en_core_web_lg module error")


def connectDB():
    try:
        conn = db.connect(db_path)
        cursor = conn.cursor()
        conn.row_factory = db.Row
        ##查询单词详情json
        cursor.execute("SELECT fb_word_detail.detail_json FROM fb_word_detail")
        global questionList
        for (child,) in cursor:
            jsonStr = str(child)
            # print(jsonStr)
            data = json.loads(jsonStr)
            word = data['word']
            part_of_speech = data['part_of_speech']
            ##经典考题
            gy_exam_link = data['gy_exam_link']

            for gy_exam in gy_exam_link:
                print(gy_exam)
                questions = gy_exam['questions']
                answer_type = gy_exam['answer_type']
                source = gy_exam['source']
                subject = gy_exam['subject']
                answer = gy_exam['answer']
                answer_a = gy_exam['answer_a']
                answer_b = gy_exam['answer_b']
                answer_c = gy_exam['answer_c']
                answer_d = gy_exam['answer_d']
                if questions == 0:
                    questions = 6   ##填空题
                elif questions == 1:
                    questions = 5   ##选择题
                question = QuestionBean(word,0,part_of_speech,questions,subject,answer,answer_a,answer_b,answer_c,answer_d,'','')
                questionList.append(question)
        cursor.close()
        conn.close()

        ##链接万禾数据库
        conn = db.connect(wh_db_path)
        cursor = conn.cursor()
        conn.row_factory = db.Row
        for questionChild in questionList:
            word = questionChild.getWord()
            part_of_speech = questionChild.getPartOfSpeech()
            cursor.execute("SELECT Wh_Word.ID,Wh_Word.Word,Wh_Word.Part_Of_Speech FROM Wh_Word WHERE Word='"+word+"' AND Part_Of_Speech='"+part_of_speech+"'")
            for data in cursor:
                questionChild.setWordID(data[0])


        #单词拼写
        spellWordQuestion()

        ##汉译英

        ##音译汉



        #将题目插入数据库
        i = 0
        for questionChild in questionList:
            cursor.execute("INSERT INTO Wh_Word_Question1 (ID,WordID,QuestionType,Subject,ItemA,ItemB,ItemC,ItemD,RightAnswer) VALUES(?,?,?,?,?,?,?,?,?)",(i,questionChild.getWordID(),questionChild.getQuestionType(),questionChild.getSubject(),questionChild.getItemA(),questionChild.getItemB(),questionChild.getItemC(),questionChild.getItemD(),questionChild.getRightAnswer()))
            i = i + 1
            conn.commit()
        # conn = db.connect(wh_db_path)
        # cursor = conn.cursor()
        # cursor.execute("INSERT INTO Wh_Word_Question1(ID,WordID,QuestionType,Subject) VALUES (?,?,?,?)",(1,2,3,"Hello world"))
        cursor.close()
        conn.close()
    except Exception as e:
        print(e)

def selectExcelWord():
    global wordList, nlpCollect
    wb = openpyxl.load_workbook(wh_excel_path)
    sheet_names = wb.sheetnames
    sheet = wb[sheet_names[0]]
    for rowNum in range(2, sheet.max_row+1):
        word = WordBean("","", "","","", "", "", "", "",
                        "")
        for columnNum in range(1,sheet.max_column+1):
            # print(sheet.cell(row=rowNum,column=columnNum).value)
            # print(rowNum,columnNum)
            cellVaule = sheet.cell(row=rowNum, column=columnNum).value
            if columnNum == 1:
                word.ID = cellVaule
            elif columnNum == 2:
                if cellVaule == '' or cellVaule == '.' or cellVaule == None:
                    continue
                nlpCollect = nlpCollect + cellVaule + " "
                word.Word = cellVaule
            elif columnNum == 3:
                word.Part_Of_Speech = cellVaule
            elif columnNum == 4:
                word.Chinese = cellVaule
                wordList.append(word)
    # for word in wordList:
    #     word.toString()




'''
    查询单词
'''
def selectWord():
    global wordList,nlpCollect
    conn = None
    if connSQL == 1:
        conn = tools.connsqlite()
    else:
        conn = tools.connsqlServer()
    cursor = conn.cursor()
    cursor.execute(
        "SELECT ID,Word,Chinese,Attachment,Part_Of_Speech,Word_Type,E_Phonetic_Symbol,U_Phonetic_Symbol,E_Audio_File,U_Audio_File FROM Wh_Word")
    for child in cursor:
        if child[1] == '' or child[1] == '.' or child[1] == None:
            continue
        nlpCollect = nlpCollect + child[1] +" "
        word = WordBean(child[0], child[1], child[2], child[3], child[4], child[5], child[6], child[7],child[8],child[9])
        wordList.append(word)
    cursor.close()
    conn.close()
    return wordList

'''
    生成单词详情Json数据
'''
def createWordDetail(wordBean:WordBean):
    id = wordBean.getID()
    word = wordBean.getWord()
    chinese = wordBean.getChinese()
    part_Of_Speech = wordBean.getPart_Of_Speech()
    e_Phonetic_Symbol = wordBean.getE_Phonetic_Symbol()
    u_Phonetic_Symbol = wordBean.getU_Phonetic_Symbol()
    e_Audio_File = wordBean.getE_Audio_File()
    u_Audio_File = wordBean.getU_Audio_File()
    keys = ["ID","Word", "Part_Of_Speech", "Chinese", "E_Phonetic_Symbol", "E_Audio_File", "U_Phonetic_Symbol",
            "U_Audio_File"]
    values = [id,word, part_Of_Speech, chinese, e_Phonetic_Symbol, e_Audio_File, u_Phonetic_Symbol, u_Audio_File]
    dictionary = dict(zip(keys, values))
    wordDetail = json.dumps(dictionary)
    return wordDetail


def family_check(word1,word2):
    if len(word1) < len(word2):
        return word2.find(word1)
    else:
        return word1.find(word2)

'''
    查询单词
    1、查询相同的拼写的单词
    2、查询不到相同拼写的单词，则会查询包含这个单词的单词
'''
def selecWordList(w:str):
    global wordList
    result = []
    result1 = []
    w = tools.trimStr(w)
    for wordBean in wordList:
        word = str(wordBean.getWord())
        reWord = tools.trimStr(word)
        if str(reWord) == str(w):
            result.append(wordBean)
        if reWord.startswith(str(w)) or reWord.endswith(w) or reWord in w:
            result1.append(wordBean)
    if len(result) == 0:
        return result1
    return result

'''
    单词拼写题目 questionType = 1
    单词中包含有括号的单词，去除括号以及括号中的字符
    单词长度大于3且不包含非法字符可以出单词拼写
'''
def spellWordQuestion():
    # selectWord()
    for wordBean in wordList:
        word = wordBean.getWord()
        wordID = wordBean.getID()
        questionWord = tools.reviserWord(word)
        #包含非法字符
        if tools.isIllegalWord(questionWord):
           continue
        if len(questionWord) <= 3:
            continue
        wordDetail = createWordDetail(wordBean)
        questionList.append(QuestionBean(word,wordID,'',1,questionWord,questionWord,'','','','','','','','',wordDetail,'',''))
    tools.writeQuestionsToXls1(questionList, "单词拼写")



'''
    英译汉
    1、通过NLP汉意相似进行匹配
    2、对筛选出来的单词进行去重复，去除相同单词不同形式的单词
'''
def englishChinese():
    global nlpCollect,nlp
    #初始化NLP工具类
    initNLP()
    #选用分数值
    threshold = 0.2
    #返回的结果数量
    resultLength = 10
    # 读取数据库中的单词
    wordList = selectWord()
    # 初始化数据库中单词为语料库，
    tokens = nlp(nlpCollect)
    #逐一检测单词
    # resultList = tools.selectSQLWord1(2130,2)

    for key,val in enumerate(wordList):
        word = val.getWord()
        wordID = val.getID()
        chinese = val.getChinese()
        if word == '' or word == '.' or word == None:
            continue
        if chinese == '' or chinese == None:
            continue
        questionWord = tools.reviserWord(word)
        part_Of_Speech = val.getPart_Of_Speech()
        # if wordType == 0:#单词
        questionChinese = chinese
        queue = []
        if questionWord == '':
            continue
        txt = nlp(questionWord)
        for token in tokens:
            score = token.similarity(txt)
            if score >= threshold and family_check(txt.text.strip(), token.text.strip()) < 0:
                if len(queue) >= resultLength:
                    index = 0  # in order to contrast
                    value = 1.0
                    for i in range(0, len(queue)):
                        if queue[i][1] < value:
                            value = queue[i][1]
                            index = i
                    if value < score:
                        queue[index] = [token.text, score]
                else:
                    queue.append([token.text, score])
            # 去除当前单词项
            for q in queue[:]:
                cq = tools.word_protoType(q[0], 'v')
                wq = tools.word_protoType(questionWord, 'v')
                if q[0] == questionWord or cq == wq or cq == questionWord:
                    queue.remove(q)
            # 去除重复项
            if len(queue) > 1:
                for q in range(len(queue)-1, -1,-1):
                    if q < len(queue):
                        if queue[q][1] >= 0.99:
                            del queue[q]
                            continue
                        #解决当前单词与上一个单词的选项重复问题
                        if len(questionList) > 0:
                            for qu in range(len(questionList) - 1, -1, -1):
                                questionBeanFlag = questionList[qu]
                                questionWordFlag = questionBeanFlag.getWord()
                                if questionWordFlag == word:
                                    itemA = questionBeanFlag.getItemA()
                                    itemB = questionBeanFlag.getItemB()
                                    itemC = questionBeanFlag.getItemC()
                                    item_A = str(questionBeanFlag.getItem_A())
                                    item_B = str(questionBeanFlag.getItem_B())
                                    item_C = str(questionBeanFlag.getItem_C())
                                    queueWord = queue[q][0]
                                    # print("queueWord:"+queueWord+",Item_A:"+str(item_A)+",Item_B:"+str(item_B)+",Item_C:"+str(item_C))
                                    if queueWord == item_A or queueWord == item_B or queueWord == item_C:
                                        del queue[q]
                                        break
                                else:
                                    break
                        if len(queue) <= q:
                            continue
                        #移除重复选项
                        copy = queue[q][0]
                        copyWord = tools.word_protoType(copy, 'v')

                        for i in range(q - 1, -1,-1):
                            if i < len(queue):
                                sample = queue[i][0]
                                if tools.word_protoType(sample,
                                                        'v') == copyWord or copy == sample or copy == word:
                                    del queue[i]
        if len(queue) < 2:
            continue
        #排序
        queue.sort(key=tools.arraySecond)
        # 添加选项
        pickOptions = tools.pickOptions(3)
        options = []
        right = 'A'
        item_A = ''
        item_B = ''
        item_C = ''
        #为选项赋值
        for i in range(0, len(pickOptions)):
            opt = pickOptions[i]
            if opt == 0:
                right = chr(ord(right) + i)
                options.append(questionChinese)
                item_A = word
            else:
                optWord = queue[-opt][0]
                optWordList = selecWordList(str(optWord))
                if len(optWordList) > 1:
                    flag = 0
                    for optWordChild in optWordList:
                        optWord_partOfSpeech = optWordChild.getPart_Of_Speech()
                        if optWord_partOfSpeech == part_Of_Speech:
                            flag = 1
                            optWordChinese = optWordChild.getChinese()
                            options.append(optWordChinese)
                            if opt == 1:
                                item_B = optWordChild.getWord()
                            else:
                                item_C = optWordChild.getWord()
                    if flag == 0:
                        optWordChild = optWordList[0]
                        optWordChinese = optWordChild.getChinese()
                        options.append(optWordChinese)
                        if opt == 1:
                            item_B = optWordChild.getWord()
                        else:
                            item_C = optWordChild.getWord()
                else:
                    optWordChild = optWordList[0]
                    optWordChinese = optWordChild.getChinese()
                    options.append(optWordChinese)
                    if opt == 1:
                        item_B = optWordChild.getWord()
                    else:
                        item_C = optWordChild.getWord()

        wordDetail = createWordDetail(val)
        question = QuestionBean(word, wordID, '', 3, questionWord, right, options[0], options[1], options[2],  '',item_A, item_B,item_C,'',wordDetail,'',
                                '')
        print(question)
        questionList.append(question)
    tools.writeQuestionsToXls1(questionList,"英译汉")

'''
汉译英题目
汉明距离：Levenshtein.hamming("arm", "arm")，单词的个数必须一致，单词之间n个位置不一样，则返回n
编辑距离：是描述由一个字串转化成另一个字串最少的操作次数，在其中的操作包括插入、删除、替换。 Levenshtein.distance(str1, str2)
计算莱文斯坦比：Levenshtein.ratio(str1, str2)

1、先进行汉明距离计算，排除等于0和大于单词长度的单词
2、如果没有单词选中，则进行
'''
def chineseEnglish():
    global nlpCollect
    threshold = 0.3
    resultLength = 10
    # selectWord()
    for wordBean in wordList:
        word = wordBean.getWord()
        chinese = wordBean.getChinese()
        wordID = wordBean.getID()
        if word == '' or word == None:
            continue
        if chinese == '' or chinese == None:
            continue
        questionWord = word
        questionWord = tools.reviserWord(questionWord)
        #计算单词相似度
        queue = tools.Levenshtein_ratio(nlpCollect,questionWord,threshold,resultLength)
        print(queue)
        # 去除当前单词及当前单词相同原型单词项
        for q in queue[:]:
            cq = tools.word_protoType(q[0], 'v')
            wq = tools.word_protoType(word,'v')
            if q[0] == word or cq == wq or cq == word:
                queue.remove(q)
            else:
                # 解决当前单词与上一个单词的选项重复问题
                if len(questionList) > 0:
                    for qu in range(len(questionList) - 1, -1, -1):
                        questionBeanFlag = questionList[qu]
                        questionWordFlag = questionBeanFlag.getWord()
                        if questionWordFlag == word:
                            itemA = questionBeanFlag.getItemA()
                            itemB = questionBeanFlag.getItemB()
                            itemC = questionBeanFlag.getItemC()
                            item_A = questionBeanFlag.getItem_A()
                            item_B = questionBeanFlag.getItem_B()
                            item_C = questionBeanFlag.getItem_C()
                            queueWord = q[0]
                            if queueWord == itemA or queueWord == itemB or queueWord == itemC:
                                queue.remove(q)
                        else:
                            break
        # 去除重复项
        if len(queue) > 1:
            for q in range(len(queue)-1, -1,-1):
                if q < len(queue):
                    copy = queue[q][0]
                    copyWord = tools.word_protoType(copy,'v')
                    for i in range(q - 1, -1,-1):
                        if i < len(queue):
                            sample = queue[i][0]
                            if tools.word_protoType(sample,'v') == copyWord or copy == sample or copy == word:
                                del queue[i]
        if len(queue) < 2:
            continue
        #添加选项
        pickOptions = tools.pickOptions(3)
        options = []
        right = 'A'
        rightPos = 0
        for i in range(0, len(pickOptions)):
            opt = pickOptions[i]
            if opt == 0:
                right = chr(ord(right) + i)
                rightPos = len(options)
                options.append(questionWord)
            else:
                options.append(queue[-opt][0])
        # print("word:"+word+",ItemA:"+options[0]+",ItemB:"+options[1]+",ItemC:"+options[2])
        #判断确定正确答案单词
        item_A = ''
        item_B = ''
        item_C = ''
        if rightPos == 0:
            item_A = selecWordList(word)[0].getChinese()
            item_B = selecWordList(options[1])[0].getChinese()
            item_C = selecWordList(options[2])[0].getChinese()
        elif rightPos ==1:
            item_B = selecWordList(word)[0].getChinese()
            item_A = selecWordList(options[0])[0].getChinese()
            item_C = selecWordList(options[2])[0].getChinese()
        elif rightPos == 2:
            item_C = selecWordList(word)[0].getChinese()
            item_A = selecWordList(options[0])[0].getChinese()
            item_B = selecWordList(options[1])[0].getChinese()

        wordDetail = createWordDetail(wordBean)
        question = QuestionBean(word, wordID, '', 2, chinese, right, options[0], options[1], options[2], '',item_A, item_B,item_C,'',wordDetail,'', '')
        # question = QuestionBean(word, wordID, '', 2, chinese, right, options[0], options[1], options[2], '','', '','','','', '')
        print(question)
        questionList.append(question)
    tools.writeQuestionsToXls1(questionList,"汉译英")

'''
    听音识词
    1、通过音标的相似度选取选项
'''
def listenSpeechKnowWord():
    global nlpCollect
    threshold = 0.3
    resultLength = 10
    # selectWord()
    for wordBeanSource in wordList:
        wordSource = wordBeanSource.getWord()
        questionWord = tools.reviserWord(wordSource)
        if questionWord == '' or questionWord == None:
            continue
        if len(questionWord) <= 3:
            continue

        wordIDSource = wordBeanSource.getID()
        e_Phonetic_SymbolSource = wordBeanSource.getE_Phonetic_Symbol()
        e_Phonetic_SymbolResult = re.sub("\'", '', e_Phonetic_SymbolSource)
        queue = []
        for wordBean in wordList:
            word = wordBean.getWord()
            questionChildWord = tools.reviserWord(word)
            chinese = wordBean.getChinese()
            e_Phonetic_Symbol = wordBean.getE_Phonetic_Symbol()
            e_Phonetic_SymbolChild = re.sub("\'", '', e_Phonetic_Symbol)
            wordID = wordBean.getID()
            if questionChildWord == '' or questionChildWord == None:
                continue
            if len(questionChildWord) <= 3:
                continue

            ratio = Levenshtein.ratio(e_Phonetic_SymbolResult, e_Phonetic_SymbolChild)
            if ratio < threshold and ratio >= 1.0:
                continue
            childResult = [questionChildWord, ratio]
            queue.append(childResult)
        queue.sort(key=tools.arraySecond)
        queue = queue[-resultLength:]
        # 词性相同，拼写相似度高
        for q in queue[:]:
            cq = tools.word_protoType(q[0], 'v')
            wq = tools.word_protoType(questionWord, 'v')
            # 去除与当前单词相同的单词
            if q[0] == questionWord or cq == wq or cq == questionWord:
                queue.remove(q)
            else:
                # 解决当前单词与上一个单词的选项重复问题
                if len(questionList) > 0:
                    for qu in range(len(questionList) - 1, -1, -1):
                        questionBeanFlag = questionList[qu]
                        questionWordFlag = questionBeanFlag.getWord()
                        if questionWordFlag == wordSource:
                            itemA = questionBeanFlag.getItemA()
                            itemB = questionBeanFlag.getItemB()
                            itemC = questionBeanFlag.getItemC()
                            queueWord = q[0]
                            if queueWord == itemA or queueWord == itemB or queueWord == itemC:
                                queue.remove(q)
                        else:
                            break
        if len(queue) > 1:
            for q in range(len(queue) - 1, -1, -1):
                if q < len(queue):
                    copy = queue[q][0]
                    copyWord = tools.word_protoType(copy, 'v')
                    for i in range(q - 1, -1, -1):
                        if i < len(queue):
                            sample = queue[i][0]
                            if tools.word_protoType(sample, 'v') == copyWord or copy == sample or copy == questionWord:
                                del queue[i]
        if len(queue) < 2:
            continue
            # 添加选项
        pickOptions = tools.pickOptions(3)
        options = []
        right = 'A'
        for i in range(0, len(pickOptions)):
            opt = pickOptions[i]
            if opt == 0:
                right = chr(ord(right) + i)
                options.append(questionWord)
            else:
                options.append(queue[-opt][0])
        wordDetail = createWordDetail(wordBeanSource)
        question = QuestionBean(wordSource, wordIDSource, '', 4, '', right, options[0], options[1], options[2],
                                '', '',
                                '', '', '', wordDetail, '', '')
        print(question)
        questionList.append(question)
    tools.writeQuestionsToXls1(questionList, "听音识词")


'''
    选词填空
    1、从语料库中读取单词的例句
    2、根据与单词相似度高度的单词作为干扰选项
'''
def selectWordsFillBlankQuestion():
    global nlpCollect
    threshold = 0.3
    resultLength = 10
    # selectWord()
    for wordBean in wordList:
        word = wordBean.getWord()
        chinese = wordBean.getChinese()
        wordID = wordBean.getID()
        if word == '' or word == None:
            continue
        if chinese == '' or chinese == None:
            continue
        questionWord = word
        sentences = tools.nlpCreateSentence(word)
        for senChild in sentences[:]:
            if len(questionList) > 0:
                questionSentencesFlag = senChild.replace(questionWord, "______", 1)
                for q in range(len(questionList) - 1, -1, -1):
                    questionBeanFlag = questionList[q]
                    subject = questionBeanFlag.getSubject()
                    questionWordFlag = questionBeanFlag.getWord()
                    if questionWordFlag == questionWord:
                        if questionSentencesFlag == subject:
                            sentences.remove(senChild)
                    else:
                        break

        if len(sentences) == 0:
            continue
        sen = sentences[0]
        questionSentences = sen.replace(questionWord, "______", 1)
        print(sen)

        index = tools.toListIndex(questionSentences,"______")

        # 计算出相似的单词数据
        queue = tools.Levenshtein_ratio(nlpCollect,questionWord,threshold,resultLength)
        print(queue)
        # 词性相同，拼写相似度高
        for q in queue[:]:
            cq = tools.word_protoType(q[0], 'v')
            wq = tools.word_protoType(word,'v')
            #去除与当前单词相同的单词
            if q[0] == word or cq == wq or cq == word:
                queue.remove(q)
            else:
                # 解决当前单词与上一个单词的选项重复问题
                if len(questionList) > 0:
                    for qu in range(len(questionList) - 1, -1, -1):
                        questionBeanFlag = questionList[qu]
                        questionWordFlag = questionBeanFlag.getWord()
                        if questionWordFlag == questionWord:
                            itemA = questionBeanFlag.getItemA()
                            itemB = questionBeanFlag.getItemB()
                            itemC = questionBeanFlag.getItemC()
                            queueWord = q[0]
                            if queueWord == itemA or queueWord == itemB or queueWord == itemC:
                                queue.remove(q)
                        else:
                            break
        if len(queue) > 1:
            for q in range(len(queue) - 1, -1, -1):
                if q < len(queue):
                    copy = queue[q][0]
                    copyWord = tools.word_protoType(copy, 'v')
                    for i in range(q - 1, -1, -1):
                        if i < len(queue):
                            sample = queue[i][0]
                            if tools.word_protoType(sample, 'v') == copyWord or copy == sample or copy == word:
                                del queue[i]
        if len(queue) < 2:
            continue
            # 添加选项
        pickOptions = tools.pickOptions(3)
        options = []
        right = 'A'
        for i in range(0, len(pickOptions)):
            opt = pickOptions[i]
            if opt == 0:
                right = chr(ord(right) + i)
                options.append(questionWord)
            else:
                options.append(queue[-opt][0])
        wordDetail = createWordDetail(wordBean)
        if index == 0:
            for o in range(len(options)):
                options[o] = options[o].capitalize()
        questionSentences = questionSentences.capitalize()
        question = QuestionBean(word, wordID, '', 5, questionSentences, right, options[0], options[1], options[2], '', '',
                                '', '', '', wordDetail, '', '')
        print(question)
        questionList.append(question)
    tools.writeQuestionsToXls1(questionList, "选词填空")

if __name__ == "__main__":
    import nltk
    selectExcelWord()
    spellWordQuestion()
    chineseEnglish()
    # englishChinese()
    listenSpeechKnowWord()
    selectWordsFillBlankQuestion()
